﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Common;
using DBUtility;
using Models;

namespace BaseDal
{
    /// <summary>
    /// PurchasePayRegistDal 类实现了付款登记的数据访问层。
    /// 该类采用单例模式，确保只有一个实例存在。
    /// </summary>
    public class PurchasePayRegistDal
    {
        // 使用 Lazy<T> 实现单例模式，确保实例的惰性初始化和线程安全。
        private static readonly Lazy<PurchasePayRegistDal> _instance = new Lazy<PurchasePayRegistDal>(() => new PurchasePayRegistDal());

        /// <summary>
        /// 获取类的单例实例。
        /// </summary>
        public static PurchasePayRegistDal Instance { get; } = _instance.Value;

        // 私有构造函数，防止外部实例化该类。
        private PurchasePayRegistDal()
        {
        }

        private const string tableH = "PurchasePayRegistHead";

        private const string tableB = "PurchasePayRegistBody";

        /// <summary>
        /// 查询数据是否存在
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public bool IsExist(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + tableH);
            strSql.Append(" where IsDelete=0 " + strWhere);
            return SqlHelper.Exists(strSql.ToString());
        }

        /// <summary>
        /// 查询SQL语句
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public StringBuilder GetSelectSql(string strWhere = "")
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ID,PPR_Number,PPR_Date,PPR_Person,PPR_CompanyId,PPR_CompanyName,PPR_DeptId,PPR_DeptName,");
            sb.Append("PPR_InvoiceNo,PPR_PayDate,PPR_SupplierId,PPR_SupplierName,PPR_Bank,PPR_BankNumber,PPR_Remark,PPR_PayAmount ");
            sb.Append("FROM " + tableH);
            sb.Append(" Where IsDelete=0 ");

            if (!string.IsNullOrEmpty(strWhere))
            {
                sb.Append(strWhere);
            }

            return sb;
        }

        /// <summary>
        /// 查询分页数据
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataTable GetPageList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder sb = GetSelectSql(strWhere);
            string countSql = PageHelper.CreateCountingSql(sb.ToString());
            recordCount = Convert.ToInt32(SqlHelper.GetSingle(countSql));
            string pageSql = PageHelper.CreatePageSql(recordCount, pageSize, pageIndex, sb.ToString(), filedOrder);
            DataSet ds = SqlHelper.Query(pageSql);
            return ds.Tables[0];
        }

        /// <summary>
        /// 查询数据集合
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public List<PurchasePayRegistHead> GetList(string strWhere)
        {
            StringBuilder sb = GetSelectSql(strWhere);
            DataSet ds = SqlHelper.Query(sb.ToString());
            return CommonOperation.ConvertDataTableToModelList<PurchasePayRegistHead>(ds.Tables[0]);
        }

        /// <summary>
        /// 根据id查询实体类
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public PurchasePayRegistHead GetModelById(decimal id)
        {
            // 查询主表
            PurchasePayRegistHead model;
            StringBuilder sb = GetSelectSql(" and ID=@id");

            SqlParameter[] parameters =
            {
                new SqlParameter("@id",id)
            };
            DataSet ds = SqlHelper.Query(sb.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                model = CommonOperation.DataTableToModel<PurchasePayRegistHead>(ds.Tables[0]);
                // 查询子表
                model.ItemList = GetItemList((int)model.ID);

                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 根据主表ID查询所有物品
        /// </summary>
        /// <param name="headID"></param>
        /// <returns></returns>
        public List<PurchasePayRegistBody> GetItemList(int headID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ID,HeadID,PPR_ApplyNo,PPR_ItemID,PPR_ItemName,PPR_Specs,PPR_Unit,PPR_Price,PPR_ApplyQty,");
            sb.Append("PPR_GetQty,PPR_NotGetQty,PPR_Paid,PPR_RegistPay,PPR_TotalCost ");
            sb.Append("FROM " + tableB);
            sb.Append(" Where IsDelete=0 and HeadID=@headID ");
            SqlParameter[] param1 = { new SqlParameter("@headID", headID) };
            DataSet ds = SqlHelper.Query(sb.ToString(), param1);
            return CommonOperation.ConvertDataTableToModelList<PurchasePayRegistBody>(ds.Tables[0]);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool AddPurchasePayRegist(PurchasePayRegistHead model, List<PurchaseRequestBody> prbList)
        {
            // 定义主表SQL语句和参数
            StringBuilder hSql = new StringBuilder();
            hSql.Append("insert into " + tableH + " (");
            hSql.Append("PPR_Number,PPR_Date,PPR_Person,PPR_CompanyId,PPR_CompanyName,PPR_DeptId,PPR_DeptName,");
            hSql.Append("PPR_InvoiceNo,PPR_Remark,PPR_PayAmount,PPR_PayDate,PPR_SupplierId,PPR_SupplierName,PPR_Bank,PPR_BankNumber)");
            hSql.Append(" OUTPUT inserted.ID values (");
            hSql.Append("@number,@date,@person,@companyId,@companyName,@deptId,@deptName,");
            hSql.Append("@invoiceNo,@remark,@payAmount,@payDate,@supplierId,@supplierName,@bank,@bankNumber)");

            string connectionString = SqlHelper.connectionString;
            Action<SqlConnection, SqlTransaction> sqlAction = (connection, transaction) =>
            {
                using (SqlCommand command1 = new SqlCommand(hSql.ToString(), connection, transaction))
                {
                    command1.CommandType = CommandType.Text;
                    command1.Parameters.AddWithValue("@number", model.PPR_Number);
                    command1.Parameters.AddWithValue("@date", model.PPR_Date);
                    command1.Parameters.AddWithValue("@person", model.PPR_Person);
                    command1.Parameters.AddWithValue("@companyId", model.PPR_CompanyId);
                    command1.Parameters.AddWithValue("@companyName", model.PPR_CompanyName);
                    command1.Parameters.AddWithValue("@deptId", model.PPR_DeptId);
                    command1.Parameters.AddWithValue("@deptName", model.PPR_DeptName);
                    command1.Parameters.AddWithValue("@invoiceNo", model.PPR_InvoiceNo);
                    command1.Parameters.AddWithValue("@remark", model.PPR_Remark);
                    command1.Parameters.AddWithValue("@payAmount", model.PPR_PayAmount);
                    command1.Parameters.AddWithValue("@payDate", model.PPR_PayDate.HasValue ? (object)model.PPR_PayDate.Value : DBNull.Value);
                    command1.Parameters.AddWithValue("@supplierId", model.PPR_SupplierId);
                    command1.Parameters.AddWithValue("@supplierName", model.PPR_SupplierName);
                    command1.Parameters.AddWithValue("@bank", model.PPR_Bank);
                    command1.Parameters.AddWithValue("@bankNumber", model.PPR_BankNumber);
                    command1.Connection = connection; // 确保连接已设置

                    // 执行命令并获取自增长ID
                    object result = command1.ExecuteScalar();
                    long newID = 0;
                    if (result != null && result is decimal) // 检查是否为null并且能否转换为decimal
                    {
                        newID = Convert.ToInt64(result);
                        model.ID = newID;
                    }

                    // 子表数据
                    List<PurchasePayRegistBody> itemList = model.ItemList;
                    if (itemList != null && itemList.Count > 0)
                    {
                        SqlCommand command2;

                        StringBuilder itemSql;
                        foreach (PurchasePayRegistBody itemModel in itemList)
                        {
                            itemSql = new StringBuilder();
                            itemSql.Append("insert into " + tableB + "(");
                            itemSql.Append("HeadID,PPR_ItemID,PPR_ItemName,PPR_Specs,PPR_Unit,PPR_Price,PPR_ApplyQty,PPR_GetQty,PPR_Paid,PPR_RegistPay,");
                            itemSql.Append("PPR_NotGetQty,PPR_TotalCost,PPR_ApplyNo)");
                            itemSql.Append(" values (");
                            itemSql.Append("@headID,@itemID,@itemName,@specs,@unit,@price,@applyQty,@getQty,@paid,@registPay,@notGetQty,@totalCost,@applyno)");

                            using (command2 = new SqlCommand(itemSql.ToString(), connection, transaction))
                            {
                                command2.CommandType = CommandType.Text;
                                command2.Parameters.AddWithValue("@headID", newID);
                                command2.Parameters.AddWithValue("@itemID", itemModel.PPR_ItemID);
                                command2.Parameters.AddWithValue("@itemName", itemModel.PPR_ItemName);
                                command2.Parameters.AddWithValue("@specs", itemModel.PPR_Specs);
                                command2.Parameters.AddWithValue("@unit", itemModel.PPR_Unit);
                                command2.Parameters.AddWithValue("@price", itemModel.PPR_Price);
                                command2.Parameters.AddWithValue("@applyQty", itemModel.PPR_ApplyQty);
                                command2.Parameters.AddWithValue("@getQty", itemModel.PPR_GetQty);
                                command2.Parameters.AddWithValue("@paid", itemModel.PPR_Paid);
                                command2.Parameters.AddWithValue("@registPay", itemModel.PPR_RegistPay);
                                command2.Parameters.AddWithValue("@notGetQty", itemModel.PPR_NotGetQty);
                                command2.Parameters.AddWithValue("@totalCost", itemModel.PPR_TotalCost);
                                command2.Parameters.AddWithValue("@applyno", itemModel.PPR_ApplyNo);

                                command2.ExecuteNonQuery();
                            }
                        }
                    }

                    // 修改采购申请子表已付款金额
                    if (prbList != null && prbList.Count > 0)
                    {
                        SqlCommand command3;

                        StringBuilder itemSql;
                        foreach (var itemModel in prbList)
                        {
                            itemSql = new StringBuilder();
                            itemSql.Append("update PurchaseRequestBody set PR_Paid=@paid ");
                            itemSql.Append("where ID=@id");

                            using (command3 = new SqlCommand(itemSql.ToString(), connection, transaction))
                            {
                                command3.CommandType = CommandType.Text;
                                command3.Parameters.AddWithValue("@headID", newID);
                                command3.Parameters.AddWithValue("@paid", itemModel.PR_Paid);
                                command3.Parameters.AddWithValue("@id", itemModel.ID);
                                command3.ExecuteNonQuery();
                            }
                        }
                    }

                }
            };

            // 调用DatabaseHelper中的静态方法ExecuteTransaction
            SqlHelper.ExecuteTransaction(sqlAction, connectionString);

            return true;
        }

        /// <summary>
        /// 删除一条数据，及子表所有相关数据
        /// </summary>
        public bool DeletePurchasePayRegist(decimal ID)
        {
            List<CommandInfo> sqllist = new List<CommandInfo>();
            //删除子表
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + tableB + " set IsDelete=1");
            strSql.Append(" where HeadID=@headID");
            SqlParameter[] parameters = { new SqlParameter("@headID", ID) };
            CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
            sqllist.Add(cmd);
            //删除主表
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("update " + tableH + " set IsDelete=1");
            strSql2.Append(" where ID=@id");
            SqlParameter[] parameters2 = { new SqlParameter("@id", ID) };
            cmd = new CommandInfo(strSql2.ToString(), parameters2);
            sqllist.Add(cmd);

            int rowsAffected = SqlHelper.ExecuteSqlTran(sqllist);
            return rowsAffected > 0;
        }

    }
}
